package com.sq.partner.manager.controller;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.mybatis.model.ActirationCardDifinition;
import com.sq.partner.manager.mybatis.model.ActirationCardReport;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.service.ActirationCardReportService;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

/**
 *
 * @author zsm 2018年7月23日
 *
 */
@Controller
@RequestMapping("/cardreport")
public class ActirationCardReportController {

	@Resource
	private ActirationCardReportService actirationCardReportService;
	
	
	@RequestMapping("/index")
	public String toCardReportPage(){
		
		return "actirationCard/cardreport/index";
	}
	@RequestMapping("/cardReportPage")
	@ResponseBody
	public Page<ActirationCardDifinition> queryPage(HttpSession session, Page<ActirationCardDifinition> page,String name,String id) throws Exception{
	    // 用户信息
        GatewayUser gatewayuser = (GatewayUser) session
                .getAttribute(Constants.SESSION_USER);
	    Map<String, Object> paramMap = SqlUtil.buildPageMap(page);
		if(StrUtil.isNotEmpty(name)){
			paramMap.put("name", "%"+name+"%");
		}
		if(StrUtil.isNotEmpty(id)){
			paramMap.put("id", id);
		}
		paramMap.put("deptNo", gatewayuser.getUserComcode().substring(0, 2));
		page.setMap(paramMap);
		page= actirationCardReportService.queryCardInfoForReport(page);
		return page;
	}
	@RequestMapping("/queryDetails")
	@ResponseBody
	public Page<ActirationCardReport> queryPageForReport(HttpSession session,Page<ActirationCardReport> page,String deptNo,String IdStr,String status) throws Exception{
		Map<String, Object> paramMap = SqlUtil.buildPageMap(page);
		if(StrUtil.isNotEmpty(deptNo)){
			paramMap.put("deptNo", deptNo);
			if (deptNo.length() != 8) {
	            paramMap.put("length",deptNo.length()+2);
			} else {
			    paramMap.put("length",deptNo.length());
			}
		}
		if(StrUtil.isNotEmpty(IdStr)){
			paramMap.put("IdStr", IdStr);
		}
		page.setMap(paramMap);
		page= actirationCardReportService.queryList(page);
		return page;
	}
	@RequestMapping("/cardPageForReport")
	@ResponseBody
	public Page<ActirationCardReport> queryPage(HttpSession session,Page<ActirationCardReport> page,String deptNo,String IdStr,String status) throws Exception{
		GatewayUser gatewayuser=(GatewayUser) session.getAttribute(Constants.SESSION_USER);
		String userComcode = gatewayuser.getUserComcode();
		Map<String, Object> paramMap = SqlUtil.buildPageMap(page);
		boolean contains = IdStr.contains(",");
		String[] arr=null;
		if(contains){
			arr = IdStr.split(",");
		}else if(StrUtil.isNotEmpty(IdStr)){
			arr=new String[]{IdStr};
		}
		if(StrUtil.isNotEmpty(deptNo)){
			paramMap.put("deptNo", deptNo);
			paramMap.put("length",deptNo.length());
		}
		if(!StrUtil.isNotEmpty(deptNo)){
			paramMap.put("deptNo", userComcode);
			paramMap.put("length", userComcode.length());
		}
		if(arr!=null){
			paramMap.put("array",arr);
		}
/*		String[] statusarray=null;
		if(status.equals("0")){
			statusarray=new String[]{"1","2","3","8"};
		}
		if(status.equals("1")){
			statusarray=new String[]{"4","5","7"};
		}
		if(status.equals("2")){
			statusarray=new String[]{"5"};
		}
		if(statusarray!=null){
			paramMap.put("statusArray", statusarray);
		}*/
		page.setMap(paramMap);
		page= actirationCardReportService.queryPage(page);
		return page;
	}
	@RequestMapping("/exportReportExcelForCard")
	public void exportCardExcel(HttpServletRequest req, HttpServletResponse resp, String deptNo,String status,@RequestParam String IdStr)throws Exception{
		Map<String, Object> map = new HashMap<String, Object>();
		List<ActirationCardReport> reportExcelData=null;
		boolean contains = IdStr.contains(",");
		String[] arr=null;
		if(contains){
			arr = IdStr.split(",");
		}else{
			arr=new String[]{IdStr};
		}
		if(StrUtil.isNotEmpty(deptNo)){
			map.put("deptNo",deptNo);
		}
		map.put("array",arr);
/*		String[] statusarray=null;
		if(status.equals("0")){	
			statusarray=new String[]{"1","2","3","8"};
		}
		if(status.equals("1")){
			statusarray=new String[]{"4","5","7"};
		}
		if(status.equals("2")){
			statusarray=new String[]{"5"};
		}
		map.put("statusArray",statusarray);*/
		reportExcelData = actirationCardReportService.queryDataForReportExcels(map);
			String fileName = "汇总机构-"+deptNo;
			List<String[]> liststr = new ArrayList<String[]>();
			NumberFormat numberFormat = NumberFormat.getNumberInstance();
			numberFormat.setMaximumFractionDigits(2);
			numberFormat.setRoundingMode(RoundingMode.DOWN);
			 String modalPremamtPp="---";
			 BigDecimal sumFrame = new BigDecimal(0);
			 BigDecimal notCommamt = new BigDecimal(0);
			 BigDecimal yesCommamt = new BigDecimal(0);
			 BigDecimal yesApp = new BigDecimal(0);
			 BigDecimal wckMoney = BigDecimal.valueOf(0.00);
			 BigDecimal yckMoney = BigDecimal.valueOf(0.00);
			 BigDecimal yappMoney = BigDecimal.valueOf(0.00);
             BigDecimal yesDiscard = new BigDecimal(0);
             BigDecimal ydiscardMoney = BigDecimal.valueOf(0.00);
			for (ActirationCardReport cardExcel : reportExcelData) {
				liststr.add(new String[]{cardExcel.getDeptNo(),cardExcel.getName(),cardExcel.getSumFrame().toString(),
				        String.valueOf(cardExcel.getModalPremamtPp()),cardExcel.getNotCommamt().toString(),
				        cardExcel.getYesCommamt().toString(),cardExcel.getYesApp().toString(),
				        cardExcel.getYesDiscard().toString(),String.valueOf(cardExcel.getWckMoney()),
				        String.valueOf(cardExcel.getYckMoney()),String.valueOf(cardExcel.getYappMoney()),
				        String.valueOf(cardExcel.getYdiscardMoney())});
				  sumFrame = sumFrame.add(cardExcel.getSumFrame());
				  notCommamt = notCommamt.add(cardExcel.getNotCommamt());
				  yesCommamt = yesCommamt.add(cardExcel.getYesCommamt());
				  yesApp = yesApp.add(cardExcel.getYesApp());
				  wckMoney = wckMoney.add(cardExcel.getWckMoney());
				  
				  yckMoney = yckMoney.add(cardExcel.getYckMoney());
				  yappMoney = yappMoney.add(cardExcel.getYappMoney());
				  
				  yesDiscard = yesDiscard.add(cardExcel.getYesDiscard());
				  ydiscardMoney = ydiscardMoney.add(cardExcel.getYdiscardMoney());
			}
			String[][] dataList = liststr.toArray(new String[0][0]);
			
			String sheetName = "激活卡统计报表";  
	        String titleName = "激活卡统计报表";    
	        int columnNumber = 12;  
	        int[] columnWidth = {38,30,15,15,15,15,15,15,15,15,15,15};  
	        String[] columnName = {"机构编码","名称","总数(张)","每单保费(元)","未出库数(张)","可激活数(张)",
	                "已激活数(张)","作废数(张)","未出库金额(元)","可激活金额(元)","已激活金额(元)","作废金额(元)"};
	        	HSSFWorkbook wb = new HSSFWorkbook();
	        	HSSFSheet sheet = wb.createSheet(sheetName);
	        	for (int i = 0; i < columnNumber; i++){
	            	for (int j = 0; j <= i; j++){  
	                    if (i == j){  
	                        sheet.setColumnWidth(i, columnWidth[j] * 256);  
	                    }  
	                }  
	            }  
	            HSSFRow row1 = sheet.createRow((int) 0);  
	            row1.setHeightInPoints(50);
	            HSSFDataFormat createDataFormat = wb.createDataFormat();
	            HSSFCellStyle style2 = wb.createCellStyle();
	            style2.setDataFormat(createDataFormat.getFormat("@"));
	            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	            style2.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);  
	            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
	            style2.setBottomBorderColor(HSSFColor.BLACK.index);  
	            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	            HSSFFont headerFont1 = (HSSFFont) wb.createFont();
	            headerFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	            headerFont1.setFontName("宋体");
	            headerFont1.setFontHeightInPoints((short) 15);  
	            style2.setFont(headerFont1);
	            HSSFCell cell1 = row1.createCell(0); 
	            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,columnNumber - 1)); 
	            cell1.setCellValue(titleName);
	            cell1.setCellStyle(style2);
	            // 第1行
	            HSSFRow row = sheet.createRow((int) 1);
	            // 表头高度  
	            row.setHeightInPoints(37);
	            // 表头单元格样式 以及表头的字体样式  
	            HSSFCellStyle style = wb.createCellStyle();
	            // 自动换行 
	            style.setWrapText(true);
	            // 居中格式
	            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    
	            style.setBottomBorderColor(HSSFColor.BLACK.index);  
	            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	            style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	            style.setBorderTop(HSSFCellStyle.BORDER_THIN);    
	            HSSFFont headerFont = (HSSFFont) wb.createFont();
	            // 字体加粗
	            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	            // 设置字体类型
	            headerFont.setFontName("宋体");
	            // 设置字体大小 
	            headerFont.setFontHeightInPoints((short) 10);
	            // 为标题样式设置字体样式 
	            style.setFont(headerFont);
	            HSSFCell cell = null;
	            for (int i = 0; i < columnNumber; i++)   
	            {  
	            	cell = row.createCell(i);  
	                cell.setCellValue(columnName[i]);
	                cell.setCellStyle(style);
	            }
	            //创建单元格 
                // 为数据内容设置特点新单元格样式1 自动换行 上下居中  
                HSSFCellStyle cellStyle = wb.createCellStyle();  
                cellStyle.setWrapText(true);// 设置自动换行  
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式  
                // 设置边框  
                cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);  
                cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
                cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
                cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
                cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
                HSSFCellStyle cellStyle1 = wb.createCellStyle();  
                cellStyle1.setWrapText(true);// 设置自动换行  
                cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个上下居中格式  
                cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中  
                // 设置边框  
                cellStyle1.setBottomBorderColor(HSSFColor.BLACK.index);  
                cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
                cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
                cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);  
                cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
                cellStyle1.setDataFormat(createDataFormat.getFormat("@"));
	            for (int i = 0; i < dataList.length; i++)   
	            {  
	                row = sheet.createRow((int) i + 2);    
	                HSSFCell datacell = null;  
	                for (int j = 0; j < columnNumber; j++){  
	                    datacell = row.createCell(j);  
	                    datacell.setCellValue(dataList[i][j]);  
	                    datacell.setCellStyle(cellStyle1);  
	                }  
	            }
	            int lastColumnNumber = 12; 
	            String[] lastColumnValue = {"合计","名称",String.valueOf(sumFrame),modalPremamtPp,
	                    String.valueOf(notCommamt),String.valueOf(yesCommamt),
	                    String.valueOf(yesApp), String.valueOf(yesDiscard),
	                    String.valueOf(numberFormat.format(wckMoney)),
	                    String.valueOf(numberFormat.format(yckMoney)),
	                    String.valueOf(numberFormat.format(yappMoney)),
	                    String.valueOf(numberFormat.format(ydiscardMoney))};
	            // 最后一行
	            HSSFRow row3 = sheet.createRow(dataList.length+2);
	            // 表头高度  
	            row3.setHeightInPoints(37);
	            // 表头单元格样式 以及表头的字体样式  
	            HSSFCellStyle style3 = wb.createCellStyle();
	            // 自动换行 
	            style.setWrapText(true);
	            // 居中格式
	            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	            style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    
	            style3.setBottomBorderColor(HSSFColor.BLACK.index);  
	            style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	            style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	            style3.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	            style3.setBorderTop(HSSFCellStyle.BORDER_THIN);    
	            HSSFFont headerFont3 = (HSSFFont) wb.createFont();
	            // 字体加粗
	            headerFont3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	            // 设置字体类型
	            headerFont3.setFontName("宋体");
	            // 设置字体大小 
	            headerFont3.setFontHeightInPoints((short) 10);
	            // 为标题样式设置字体样式 
	            style3.setFont(headerFont3);
	            sheet.addMergedRegion(new CellRangeAddress(dataList.length+2, dataList.length+2, 0,1));
	            
	            for (int i = 0; i < lastColumnNumber; i++)   
	            {  
	            	cell = row3.createCell(i);  
	                cell.setCellValue(lastColumnValue[i]);
	                cell.setCellStyle(style3);
	            }
	            resp.setContentType("application/ms-excel;charset=UTF-8");  
	            //20190216解决文件名乱码（浏览器兼容问题） start
	            //resp.setHeader("Content-Disposition","attachment;filename="+ new String(fileName.getBytes(),"iso-8859-1")+".xls");
                String userAgent = req.getHeader("user-agent").toLowerCase();  
                if (userAgent.contains("msie") || userAgent.contains("trident")
                        || userAgent.contains("edge")) {
                    //IE浏览器
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                } else {
                    //非IE浏览器
                    fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");  
                }
                resp.setHeader("Content-Disposition","attachment;filename="+ fileName +".xls");
                //20190216解决文件名乱码（浏览器兼容问题） end
                OutputStream out = resp.getOutputStream();  
	            try {
	            	wb.write(out); 
	            } catch (Exception e) {
	            	e.printStackTrace();  
	            } finally {
	            	out.close();  
	            }
			}
	/**
     * 查询详情页面 lfd added 20181102
     * @return
     */
    @RequestMapping("/details")
    public String CardReportDetails(){
        return "actirationCard/cardreport/details";
    }
}
